/**
 * DaoZxz.java
 *2013年10月7日下午6:38:10
 *LINKT
 */
package com.jskj.dao;

import java.sql.ResultSet;
import java.sql.SQLException;

import com.jskj.commons.DBConn;

/**
 * @author LINKT
 *
 */
public class DaoZxz {

        public String getZxzList(int page ,int limit,String sbyf,String swjg_dm,String jyz_sh) {
                String sql="";
                String sql1="";
                DBConn conn=new DBConn();
                int rowEnd=page*limit;
                String str="";
                if (!jyz_sh.equals("")) {
                        sql1=" and g.jyz_sh='"+jyz_sh+"' ";
                }
                if (!swjg_dm.equals("") && !swjg_dm.equals("11305000000")) {
                        sql1+=" and g.jyz_swjg='"+swjg_dm+"'";
                }
                sql="select f.*,g.jyz_mc,g.jyz_sh,g.jyz_bh from "+
                                "(select ISNULL(e.jyz_zxz,e.jyz_bh) as jyz_zxz,sum(e.yp_93) as yp_93,sum(e.yp_97) as yp_97,sum(e.yp_0) as yp_0,sum(e.yp_other) as yp_other,sum(e.yp_sum) as yp_sum,sbyf from "+
                                "(select d.jyz_zxz,c.jyz_mc,c.jyz_sh,b.* from "+
                                        "(select a.jyz_bh , "+
                                                "sum(case when [yp]='102' then [ylsum] else 0 end)as yp_93, "+
                                                "sum(case when [yp]='104' then [ylsum] else 0 end)as yp_97, "+
                                                "sum(case when [yp]='202' then [ylsum] else 0 end)as yp_0, "+
                                                "sum(case when [yp]not in('102','104','202') then [ylsum] else 0 end)as yp_other, "+
                                                "sum(ylsum) as yp_sum, "+
                                                "'"+sbyf+"' as sbyf "+
                                        "from v_yzsum a "+
                                        "where a.ny='"+sbyf+"' "+
                                        "group by a.jyz_bh) b "+
                                "join jyz c on (b.jyz_bh=c.jyz_bh and c.jyz_bz='Y') "+
                                "left join jyzgx d on (b.jyz_bh =d.jyz_ptz) ) e "+
                                "group by e.jyz_zxz,e.sbyf,e.jyz_bh) f "+
                                "join jyz g on(f.jyz_zxz=g.jyz_bh) "+
                                "where 1=1 "+sql1;
                int totalcount=conn.SelectSQLCount(sql);
                StringBuffer sb=new StringBuffer();
                String sSQL="select * from ("+
                                "    select top "+limit+" * from("+
                                "   select top "+rowEnd+" * "+
                                "    from ("+sql+") as aa order by jyz_bh) as bb order by jyz_bh desc ) as cc order by jyz_bh ";
                ResultSet rs=conn.SelectSQL(sSQL);
                try {
                        while (rs.next()) {
                                sb.append("{\"jyz_mc\":\""+rs.getString("jyz_mc")+"\",\"jyz_sh\":\""+rs.getString("jyz_sh")+"\",\"sbyf\":\""+rs.getString("sbyf")+"\","
                                                + "\"yp_93\":\""+rs.getString("yp_93")+"\","
                                                + "\"yp_97\":\""+rs.getString("yp_97")+"\","
                                                + "\"yp_0\":\""+rs.getString("yp_0")+"\","
                                                + "\"yp_other\":\""+rs.getString("yp_other")+"\","
                                                + "\"yp_sum\":\""+rs.getString("yp_sum")+"\""
                                                                + "},");
                        }
                        rs.close();
                        conn.Close();
                        if (sb.length()>0) {
                                sb=sb.deleteCharAt(sb.length()-1);
                        }
                        str="{\"totalCount\":"+totalcount+",\"items\":["+sb.toString()+"]}";;
                } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                }
                
                return str;
        }
}
